package db;


import beans.ExpenseRecord;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ExpenseRecordDAO {
    private Connection connection;

    public ExpenseRecordDAO(Connection connection) {
        this.connection = connection;
    }

    // 获取所有费用记录
    public List<ExpenseRecord> getAllExpenseRecords() throws SQLException {
        List<ExpenseRecord> records = new ArrayList<>();
        String sql = "SELECT * FROM expenserecords";

        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                ExpenseRecord record = new ExpenseRecord(
                        rs.getInt("ExpenseID"),
                        rs.getString("Date"),
                        rs.getString("CardID"),
                        rs.getString("Type"),
                        rs.getString("Description"),
                        rs.getBigDecimal("Amount"),
                        rs.getInt("ApprovedBy"),
                        determineStatus(rs.getInt("ApprovedBy")) // 根据ApprovedBy确定状态
                );
                records.add(record);
            }
        }
        return records;
    }

    // 根据条件查询费用记录
    public List<ExpenseRecord> searchExpenseRecords(String cardID, String status,
                                                    String startDate, String endDate) throws SQLException {
        List<ExpenseRecord> records = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM expenserecords WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (cardID != null && !cardID.isEmpty()) {
            sql.append(" AND CardID LIKE ?");
            params.add("%" + cardID + "%");
        }

        if (startDate != null && !startDate.isEmpty()) {
            sql.append(" AND Date >= ?");
            params.add(startDate);
        }

        if (endDate != null && !endDate.isEmpty()) {
            sql.append(" AND Date <= ?");
            params.add(endDate);
        }

        try (PreparedStatement pstmt = connection.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                ExpenseRecord record = new ExpenseRecord(
                        rs.getInt("ExpenseID"),
                        rs.getString("Date"),
                        rs.getString("CardID"),
                        rs.getString("Type"),
                        rs.getString("Description"),
                        rs.getBigDecimal("Amount"),
                        rs.getInt("ApprovedBy"),
                        determineStatus(rs.getInt("ApprovedBy"))
                );

                // 如果状态条件存在，只添加匹配状态的记录
                if (status == null || status.isEmpty() ||
                        record.getStatus().equalsIgnoreCase(status)) {
                    records.add(record);
                }
            }
        }
        return records;
    }

    // 根据ApprovedBy字段确定状态
    private String determineStatus(int approvedBy) {
        if (approvedBy == 0) {
            return "ongoing"; // 正在进行
        } else if (approvedBy == -1) {
            return "review";   // 审核中
        } else {
            return "completed"; // 已完成
        }
    }

    public void closeConnection() {
    }
}
